package com.sharp.portal.service.excel;

import com.sharp.framework.dao.entity.DictionaryItem;
import com.sharp.framework.service.excel.ExcelService;
import com.sharp.kernel.utils.JsonUtil;
import com.sharp.kernel.utils.MathUtil;
import com.sharp.kernel.utils.StringUtil;
import com.sharp.portal.constant.anno.PortalField;
import com.sharp.portal.vo.FieldDisplayVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.RoundingMode;
import java.util.*;

import static org.apache.poi.ss.usermodel.DateUtil.getJavaDate;

/**
 * Title: PortalExcelService
 * Description: Copyright: Copyright (c) 2022 Company: Sharp Ltd.
 *
 * @author Sharp
 * @date 2022/10/11 16:36
 */
@Slf4j
@Service
public class PortalExcelService extends ExcelService {

    public void generateExcel(Map<String, List<LinkedHashMap<String, String>>> excelMap,
                              LinkedHashMap<String, FieldDisplayVO> fieldDisplayMap, OutputStream outputStream) {
        XSSFWorkbook xssfWorkbook = generateExcelByFieldDisplay(excelMap, fieldDisplayMap);
        try {
            outputStream.flush();
            xssfWorkbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            log.error("生成EXCEL失败", e);
        }
    }

    public XSSFWorkbook generateExcelByFieldDisplay(Map<String, List<LinkedHashMap<String, String>>> excelMap,
                                                    LinkedHashMap<String, FieldDisplayVO> fieldDisplayMap) {
        XSSFWorkbook wb = new XSSFWorkbook();
        for (Map.Entry<String, List<LinkedHashMap<String, String>>> sheetEntry : excelMap.entrySet()) {
            List<LinkedHashMap<String, String>> sheetData = sheetEntry.getValue();
            boolean template = CollectionUtils.isEmpty(sheetData);
            XSSFSheet sheet = createSheet(wb, sheetEntry.getKey(), fieldDisplayMap, template);
            XSSFCellStyle cellStyle = setContentCellStyle(wb);
            if (!template) {
                for (int line = 0; line < sheetData.size(); line++) {
                    XSSFRow row = sheet.createRow(line + 1);
                    int columnIndex = 0;
                    for (Map.Entry<String, String> entry : sheetData.get(line).entrySet()) {
                        if (StringUtil.convertSwitch(fieldDisplayMap.get(entry.getKey()).getHidden())) {
                            continue;
                        }
                        XSSFCell cell = row.createCell(columnIndex++);
                        cell.setCellStyle(cellStyle);
                        String value = entry.getValue();
                        cell.setCellValue(value);
                    }
                }
            }
            formatSheet(wb, fieldDisplayMap.keySet());
        }
        return wb;
    }

    private XSSFSheet createSheet(XSSFWorkbook wb, String sheetName, Map<String, FieldDisplayVO> fieldTitleMap,
                                  boolean template) {
        XSSFSheet sheet = wb.createSheet(sheetName);
        sheet.createFreezePane(0, 1, 0, 1);
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCellStyle colorStyle = setTitleCellStyle(wb);
        XSSFCellStyle textStyle = buildXssfCellTextStyle(wb);
        int index = 0;
        for (Map.Entry<String, FieldDisplayVO> entry : fieldTitleMap.entrySet()) {
            if (StringUtil.convertSwitch(entry.getValue().getHidden())) {
                continue;
            }
            if (template && !StringUtil.convertSwitch(entry.getValue().getUpload())) {
                continue;
            }
            XSSFCell titleCell = titleRow.createCell(index);
            titleCell.setCellStyle(colorStyle);
            titleCell.setCellValue(entry.getValue().getDisplayName());
            if (StringUtils.isNotBlank(entry.getValue().getReference())) {
                List<String> dataList = new ArrayList<>();
                List<DictionaryItem> dictItemList = frameCacheService.getDictItemList(entry.getValue().getReference());
                if (CollectionUtils.isNotEmpty(dictItemList)) {
                    dictItemList.forEach(item -> {
                        dataList.add(item.getRemark());
                    });
                }
                setValidateData(sheet, dataList.toArray(new String[0]), index);
            }
            sheet.setDefaultColumnStyle(index, textStyle);
            index++;
        }
        return sheet;
    }

    public List<LinkedHashMap<String, String>> readFromExcel(File file, LinkedHashMap<String, FieldDisplayVO> fieldDisplayMap) {
        List<LinkedHashMap<String, String>> objList = null;
        FileInputStream fs;
        String filePath = file.getAbsolutePath();
        try {
            fs = new FileInputStream(filePath);
            try {
                // 使用POI提供的方法得到excel的信息
                fs = new FileInputStream(filePath);
                XSSFWorkbook wb = new XSSFWorkbook(fs);
                objList = readFromExcel(wb, fieldDisplayMap);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                fs.close();
            }
        } catch (Exception e) {
            log.error("生成EXCEL失败", e);
        }
        return objList;
    }

    private List<LinkedHashMap<String, String>> readFromExcel(XSSFWorkbook wb,
                                                    LinkedHashMap<String, FieldDisplayVO> fieldDisplayMap) {
        List<LinkedHashMap<String, String>> resList = new ArrayList<>();
        // 获取到工作表，因为一个excel可能有多个工作表
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);
            int maxline = sheet.getLastRowNum();
            Map<String, FieldDisplayVO> fieldTitleMap = buildDisplayFieldMap(fieldDisplayMap);
            Map<FieldDisplayVO, Integer> fieldColumnMap = buildFieldColumnMap(0, sheet, fieldTitleMap);
            if (fieldColumnMap.entrySet().size() > 0) {
                for (int line = 1; line <= maxline; line++) {
                    XSSFRow row = sheet.getRow(line);
                    if (row == null) {
                        continue;
                    }
                    LinkedHashMap<String, String> res = new LinkedHashMap<>();
                    boolean update = false;
                    for (Map.Entry<FieldDisplayVO, Integer> entry : fieldColumnMap.entrySet()) {
                        XSSFCell cell = row.getCell(entry.getValue());
                        Object obj = null;
                        if (cell != null) {
                            switch (cell.getCellType()) {
                                case STRING:
                                    obj = cell.getStringCellValue();
                                    break;
                                case NUMERIC:
                                    if (StringUtils.equals(entry.getKey().getDataType(),
                                            PortalField.DataType.STRING.name())) {
                                        obj = MathUtil.convert(cell.getNumericCellValue())
                                                .setScale(0, RoundingMode.HALF_UP).toPlainString();
                                    } else if (StringUtils.equals(entry.getKey().getDataType(),
                                            PortalField.DataType.DATETIME.name()) ||
                                            StringUtils.equals(entry.getKey().getDataType(),
                                                    PortalField.DataType.DATE.name())) {
                                        obj = getJavaDate(cell.getNumericCellValue());
                                    } else {
                                        obj = cell.getNumericCellValue();
                                    }
                                    break;
                                case BOOLEAN:
                                    obj = cell.getBooleanCellValue();
                                    break;
                                default:

                            }
                        }
                        if (obj != null) {
                            try {
                                String value = JsonUtil.readJson(obj, String.class);
                                update = true;
                                res.put(entry.getKey().getProperty(), value);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                    if (update) {
                        resList.add(res);
                    }
                }
            }
        }
        return resList;
    }

    private Map<String, FieldDisplayVO> buildDisplayFieldMap(LinkedHashMap<String, FieldDisplayVO> fieldDisplayMap) {
        Map<String, FieldDisplayVO> map = new HashMap<>(fieldDisplayMap.size());
        fieldDisplayMap.forEach((property, fieldDisplay) -> {
            map.put(fieldDisplay.getDisplayName(), fieldDisplay);
        });
        return map;
    }

    private Map<FieldDisplayVO, Integer> buildFieldColumnMap(int startRow, XSSFSheet sheet,
                                                             Map<String, FieldDisplayVO> fieldTitleMap) {
        Map<FieldDisplayVO, Integer> map = new LinkedHashMap<>(fieldTitleMap.entrySet().size());
        XSSFRow titleRow = sheet.getRow(startRow);
        int maxColumn = titleRow.getLastCellNum();
        for (int column = 0; column < maxColumn; column++) {
            XSSFCell cell = titleRow.getCell(column);
            if (cell != null) {
                for (Map.Entry<String, FieldDisplayVO> entry : fieldTitleMap.entrySet()) {
                    if (entry.getKey().equals(cell.getStringCellValue())) {
                        map.put(entry.getValue(), column);
                    }
                }
            }
        }
        return map;
    }
}
